package com.zhangtao.note.dao;
import com.zhangtao.note.po.NoteType;
import com.zhangtao.note.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class NoteTypeDao {
    public List<NoteType> findTypeListByUserId(Integer userId){
        // 1. 定义SQL语句
        String sql = "select typeId,typeName,userId from tb_note_type where userId = ? ";
        // 2. 设置参数列表
        List<Object> params = new ArrayList<>();
        params.add(userId);
        // 3. 调用BaseDao的查询方法，返回集合
        List<NoteType> list = BaseDao.queryRows(sql, params, NoteType.class);
        // 4. 返回集合
        return list;
    }

    //删除指定的类型记录，并返回受影响的行数
    public int deleteTypeById(String typeId) {
        //1.定义sql
        String sql = "delete  from tb_note_type where typeId = ?";
        //2.设置参数集合
        List<Object> params = new ArrayList<>();
        params.add(typeId);
        //调用basedao
        int row = BaseDao.executeUpdate(sql, params);
        return row;
    }

    //通过类型id查询云记录的数量
    public long findNoteCountByTypeId(String typeId) {
        //1.定义sql
        String sql = "select count(1) from tb_note where typeId = ?";
        //2.设置参数集合
        List<Object> params = new ArrayList<>();
        params.add(typeId);
        //调用basedao
        long count = (long) BaseDao.findSingleValue(sql, params);
        return count;
    }

    //验证名字
    public Integer checkTypeName(String typeName, Integer userId, String typeId) {
        //定义sql
        String sql = "select * from tb_note_type where userId = ? and typeName = ?";
        //设置参数
        List<Object> params = new ArrayList<>();
        params.add("userId");
        params.add(typeName);
        //执行查询操作
        NoteType noteType = (NoteType) BaseDao.queryRow(sql, params, NoteType.class);
        //如果对象为空，表示可用
        if (noteType == null){
            return 1;
        }else{
            //若不为空
            if (typeId.equals(noteType.getTypeId().toString())){
                return 1;
            }
        }
        return 0;
    }

    //添加操作
    public Integer addType(String typeName, Integer userId) {
        Integer key = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //得到数据库连接
            connection = DBUtil.getConnetion();
            //定义sql语句
            String sql = "insert into tb_note_type(typeName,userId) values(?,?)";
            //预编译
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //设置参数
            preparedStatement.setString(1, typeName);
            preparedStatement.setInt(2, userId);
            //执行更新，返回受影响的行数
            int row = preparedStatement.executeUpdate();
            //判断受影响的行数
            if(row > 0){
                //获取主键
                resultSet = preparedStatement.getGeneratedKeys();
                //得到主键的值
                if(resultSet.next()){
                    key = resultSet.getInt(1);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet,preparedStatement,connection);
        }
        return key;
    }

    //更新修改
    public Integer updateType(String typeName, String typeId) {
        //定义sql
        String sql = "update tb_note_type set typeName = ? where typeId = ?";
        //设置参数
        List<Object> params = new ArrayList<>();
        params.add(typeName);
        params.add(typeId);
        //调用basedao的更新方法
        int row = BaseDao.executeUpdate(sql, params);
        return row;
    }
}
